﻿namespace etracks.Helpers
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Data.Common;
    using Npgsql;
    using System.Data;

    public class PostgresHelper : IDatabaseHelper
    {
        private DbConnection _Connection = null;

        public string ConnectionString 
        { 

            get
            {
                if (this._Connection == null)
                    this._Connection = new NpgsqlConnection();

                return this._Connection.ConnectionString;
            }
            set
            {
                if (this._Connection == null)
                    this._Connection = new NpgsqlConnection();

                this._Connection.ConnectionString = value;
            }

        } // public string ConnectionString 
        
        public DbConnection Connection
        {

            get
            {
                if (this._Connection == null)
                    this._Connection = new NpgsqlConnection();

                return this._Connection;
            }

            set
            {
                this._Connection = value;
            }

        } // public DbConnection Connection

        public DbParameter GetParameter(string parameterName, object parameterValue)
        {
            return new NpgsqlParameter(parameterName, parameterValue);
        }

        public DataSet GetDataSet(string functionName, List<DbParameter> parametersList)
        {
            using (var conn = Connection)
            {
                DbCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = functionName;

                cmd.Parameters.Clear();
                foreach (DbParameter parameter in parametersList)
                    cmd.Parameters.Add(parameter);
                
                DbDataAdapter da = new NpgsqlDataAdapter((NpgsqlCommand)cmd);

                DataSet ds = new DataSet();

                da.Fill(ds);

                return ds;
            }

        } // public DataSet GetDataSet(string functionName, List<DbParameter> parametersList)

        public void Execute(string functionName, List<DbParameter> parametersList)
        {
            using (var conn = Connection)
            {
                DbCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = functionName;

                cmd.Parameters.Clear();
                foreach (DbParameter parameter in parametersList)
                    cmd.Parameters.Add(parameter);

                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }

        } // public void Execute(string functionName, List<DbParameter> parametersList)

        //public DbCommand GetCommand()
        //{
        //    throw new NotImplementedException();
        //}

        //public DbDataAdapter GetDataAdapter()
        //{
        //    throw new NotImplementedException();
        //}

        //public DbDataReader GetDataReader()
        //{
        //    throw new NotImplementedException();
        //}

        // Define a bunch of static methods that will always use default connection (connection string name: postgres_default)
        public static object GetObject(string sql)
        {
            object result = null;

            using (NpgsqlConnection connection = new NpgsqlConnection(ConfigHelper.GetConnectionString("postgres_default")))
            {
                NpgsqlCommand cmd = connection.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;

                connection.Open();
                result = cmd.ExecuteScalar();
                connection.Close();
            }

            return result;
        }

        public static DataTable GetDataTable(string sql)
        {
            DataSet ds = new DataSet();

            using (NpgsqlConnection connection = new NpgsqlConnection(ConfigHelper.GetConnectionString("postgres_default")))
            {
                NpgsqlCommand cmd = connection.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;

                NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
                da.Fill(ds);

                if (ds.Tables.Count > 0)
                    return ds.Tables[0];
                else
                    return null;
            }
        }

        public static DataTable GetDataTable(string sql, Dictionary<string, object> parameters)
        {
            DataSet ds = new DataSet();

            using (NpgsqlConnection connection = new NpgsqlConnection(ConfigHelper.GetConnectionString("postgres_default")))
            {
                NpgsqlCommand cmd = connection.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;

                foreach (KeyValuePair<string, object> entry in parameters)
                    cmd.Parameters.AddWithValue(entry.Key, entry.Value);
                    
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
                da.Fill(ds);

                if (ds.Tables.Count > 0)
                    return ds.Tables[0];
                else
                    return null;
            }
        }

        public static int Execute(string sql, Dictionary<string, object> parameters)
        {
            DataSet ds = new DataSet();

            using (NpgsqlConnection connection = new NpgsqlConnection(ConfigHelper.GetConnectionString("postgres_default")))
            {
                NpgsqlCommand cmd = connection.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;

                foreach (KeyValuePair<string, object> entry in parameters)
                    cmd.Parameters.AddWithValue(entry.Key, entry.Value);

                connection.Open();
                int rowsAffected = cmd.ExecuteNonQuery();
                connection.Close();

                return rowsAffected;
            }
        }

        public static object GetObject(string sql, Dictionary<string, object> parameters)
        {
            object result = null;

            using (NpgsqlConnection connection = new NpgsqlConnection(ConfigHelper.GetConnectionString("postgres_default")))
            {
                NpgsqlCommand cmd = connection.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;

                foreach (KeyValuePair<string, object> entry in parameters)
                    cmd.Parameters.AddWithValue(entry.Key, entry.Value);

                connection.Open();
                result = cmd.ExecuteScalar();
                connection.Close();
            }

            return result;
        }

    } // public class PostgresHelper : IDatabaseHelper

} // namespace etracks.Helpers